﻿using System;
using System.Collections.Generic;
using System.Data.Common;
// Krzysztof Heim
// pktools.codeplex.com

namespace PKTools.Database
{    
    public abstract class SqlObject
    {
        [NonSerialized]
        protected string
            __table;
        [NonSerialized]
        protected string[]
            __fields;
        [NonSerialized]
        protected Func<object>[]
            __map;
        [NonSerialized]
        protected bool[]
            __changed,
            __isPK;
        [NonSerialized]
        protected int
            __identity;

        bool
            __deleted;

        public void ResetChanges()
        {
            for (int i = 0; i < this.__changed.Length; i++)
                this.__changed[i] = false;
        }

        protected abstract void LoadData(DbDataReader reader);

        protected virtual void SetIdentity(int value)
        {
        }

        protected virtual void LoadRecordStamp(DbDataReader reader)
        {
        }

        public static T? ToNullable<T>(object value) where T : struct
        {
            return value == DBNull.Value ? null : new T?((T)value);
        }

        public static T ToObject<T>(object value) where T : class
        {
            return value == DBNull.Value ? null : value as T;
        }

        public static string EmptyToNull(object value)
        {
            if (value == DBNull.Value)
                return null;

            var
                temp = value as string;

            return temp == string.Empty ? null : temp;  // this is for db's where empty string as null
        }

        public static T LoadRecord<T>(Sql sql, string query) where T : SqlObject, new()
        {
            using (new Stoper("load record", () => query))
            using (var reader = sql.ExecuteReader(query))
                if (reader.Read())
                {
                    var
                        record = new T();
                    record.LoadData(reader);
                    record.LoadRecordStamp(reader);
                    return record;
                }

            return null;
        }

        public static List<T> LoadList<T>(Sql sql, string query) where T : SqlObject, new()
        {
            var
                list = new List<T>();

            using (new Stoper("load list", () => query))
            using (var reader = sql.ExecuteReader(query))
                while (reader.Read())
                {
                    var
                        record = new T();
                    record.LoadData(reader);
                    record.LoadRecordStamp(reader);
                    list.Add(record);
                }

            return list;
        }

        protected virtual string SetupSetQuery(string setQuery)
        {
            return setQuery.Remove(setQuery.Length - 1);
        }

        protected virtual string SetupPKQuery(Sql sql, string pkQuery, bool validateStamp)
        {
            return pkQuery.Remove(pkQuery.Length - 5);
        }

        public void Update(Sql sql, bool validateStamp = true)
        {
            if (this.__deleted)
                throw new Exception("object already deleted");
            if (sql.Transaction == null)
                throw new Exception("transaction required");

            sql.Clear();

            bool
                any = false;
            string
                setQuery = string.Empty,
                pkQuery = string.Empty;

            for (int i = 0; i < this.__fields.Length; i++)
            {
                if (this.__isPK[i])
                {
                    pkQuery += sql.EscapeName(this.__fields[i]) + "=@" + this.__fields[i] + " AND ";
                    sql.AddInput(this.__fields[i], this.__map[i]());
                }

                if (this.__identity != i && this.__changed[i])
                {
                    setQuery += sql.EscapeName(this.__fields[i]) + "=@_" + this.__fields[i] + ',';
                    sql.AddInput('_' + this.__fields[i], this.__map[i]());
                    any = true;
                }
            }

            if (!any)
                return;

            sql.ObjectsToReset.Add(this);

            if (sql.ExecuteNonQuery("UPDATE {0} SET {1} WHERE {2}",
                    sql.EscapeName(this.__table),
                    SetupSetQuery(setQuery),
                    SetupPKQuery(sql, pkQuery, validateStamp)) != 1)
                throw new Exception(this.RecordNotFoundText);

            AfterUpdate();
        }

        protected virtual void AfterUpdate()
        {
        }

        public void Delete(Sql sql, bool validateStamp = true)
        {
            sql.Clear();

            string
                pkQuery = string.Empty;

            for (int i = 0; i < this.__fields.Length; i++)
                if (this.__isPK[i])
                {
                    pkQuery += sql.EscapeName(this.__fields[i]) + "=@" + this.__fields[i] + " AND ";
                    sql.AddInput(this.__fields[i], this.__map[i]());
                }

            if (sql.ExecuteNonQuery("DELETE {0} WHERE {1}",
                    sql.EscapeName(this.__table),
                    SetupPKQuery(sql, pkQuery, validateStamp)) != 1)
                throw new Exception(this.RecordNotFoundText);

            this.__deleted = true;
        }

        public void Insert(Sql sql)
        {
            sql.Clear();

            bool
                any = false;
            string
                fieldsQuery = string.Empty,
                valuesQuery = string.Empty;

            for (int i = 0; i < this.__fields.Length; i++)
            {
                if (this.__identity != i && this.__map[i]() != null)
                {
                    fieldsQuery += sql.EscapeName(this.__fields[i]) + ',';
                    valuesQuery += '@' + this.__fields[i] + ',';
                    sql.AddInput(this.__fields[i], this.__map[i]());
                    any = true;
                }

                this.__changed[i] = false;
            }

            if (!any)
                throw new Exception("no values to insert");

            sql.ExecuteNonQuery("INSERT {0} ({1}) VALUES ({2})",
                sql.EscapeName(this.__table),
                fieldsQuery.Remove(fieldsQuery.Length - 1),
                valuesQuery.Remove(valuesQuery.Length - 1));

            if (this.__identity != -1)
                SetIdentity((int)sql.GetIdentity());
        }

        protected virtual string RecordNotFoundText
        {
            get { return "record not found"; }
        }
    }
}
